<?php
/**
 * Created by PhpStorm.
 * User: Frings
 * Date: 2016/9/19
 * Time: 9:53
 */

namespace app\models;


use app\librarys\Tools;

Class Activity
{
    /**
     * 获取特价活动数量
     * @param $current_user_id
     * @param $current_institution_code
     * @return false|null|string
     */
    public static function getBargainCount($current_user_id, $current_institution_code, $search_institution_code, $name, $start_datetime, $end_datetime)
    {
        $sql = '';
        if (Permission::isSystemAdmin()) {
            $sql = 'SELECT count(*) FROM t_activity_bargain t1 INNER JOIN t_institution t2 ON t2.code = t1.institution_code WHERE t1.is_deleted = 0 ';
        } else {
            $sql = 'SELECT count(*) FROM (
                      SELECT tb0.*
                      FROM t_activity_bargain tb0 INNER JOIN t_institution tb1 ON tb1.code = tb0.institution_code
                      WHERE tb1.code = :institution_code AND tb0.is_deleted = 0
                      UNION ALL
                      SELECT tb0.*
                      FROM t_activity_bargain tb0 INNER JOIN t_institution tb1 ON tb1.code = tb0.institution_code
                      WHERE tb1.parent_code = :institution_code AND tb0.is_deleted = 0
                    ) as t1 where 1 = 1 ';
        }

        if ($search_institution_code != '') {
            $sql = $sql . ' AND t1.institution_code = :search_institution_code ';
        }

        if ($name != '') {
            $sql = $sql . ' AND t1.name Like :name';
        }

        if ($start_datetime != '') {
            $sql = $sql . ' AND t1.start_datetime >= :start_datetime';
        }

        if ($end_datetime != '') {
            $sql = $sql . ' AND t1.end_datetime <= :end_datetime';
        }

        $cmd = \Yii::$app->db->createCommand($sql);

        if ($search_institution_code != '') {
            $cmd->bindValue(':search_institution_code', $search_institution_code);
        }

        if (!Permission::isSystemAdmin()) {
            $cmd->bindValue(':institution_code', $current_institution_code);
        }

        if ($name != '') {
            $cmd->bindValue(':name', '%' . $name . '%');
        }

        if ($start_datetime != '') {
            $cmd->bindValue(':start_datetime', $start_datetime);
        }

        if ($end_datetime != '') {
            $cmd->bindValue(':end_datetime', $end_datetime);
        }

        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 获取特价活动列表
     * @param $page
     * @param $pagesize
     * @param $current_user_id
     * @param $current_institution_code
     * @return array
     */
    public static function getBargainList($page, $pagesize, $current_user_id, $current_institution_code, $search_institution_code, $name, $start_datetime, $end_datetime)
    {
        $sql = '';
        if (Permission::isSystemAdmin()) {
            $sql = 'SELECT t1.*,t2.name AS institution_name FROM t_activity_bargain t1 INNER JOIN t_institution t2 ON t2.code = t1.institution_code WHERE t1.is_deleted = 0  ';
        } else {
            $sql = 'SELECT t1.*,t2.name AS institution_name FROM (
                      SELECT tb0.*
                      FROM t_activity_bargain tb0 INNER JOIN t_institution tb1 ON tb1.code = tb0.institution_code
                      WHERE tb1.code = :institution_code AND tb0.is_deleted = 0
                      UNION ALL
                      SELECT tb0.*
                      FROM t_activity_bargain tb0 INNER JOIN t_institution tb1 ON tb1.code = tb0.institution_code
                      WHERE tb1.parent_code = :institution_code AND tb0.is_deleted = 0
                    ) as t1 INNER JOIN t_institution t2 ON t2.code = t1.institution_code
                    WHERE 1 = 1 ';
        }

        if ($search_institution_code != '') {
            $sql = $sql . ' AND t1.institution_code = :search_institution_code ';
        }

        if ($name != '') {
            $sql = $sql . ' AND t1.name Like :name';
        }

        if ($start_datetime != '') {
            $sql = $sql . ' AND t1.start_datetime >= :start_datetime';
        }

        if ($end_datetime != '') {
            $sql = $sql . ' AND t1.end_datetime <= :end_datetime';
        }

        $sql = $sql . ' ORDER BY start_datetime DESC LIMIT :start,:pagesize';

        $cmd = \Yii::$app->db->createCommand($sql);

        if ($search_institution_code != '') {
            $cmd->bindValue(':search_institution_code', $search_institution_code);
        }

        if (!Permission::isSystemAdmin()) {
            $cmd->bindValue(':institution_code', $current_institution_code);
        }

        if ($name != '') {
            $cmd->bindValue(':name', '%' . $name . '%');
        }

        if ($start_datetime != '') {
            $cmd->bindValue(':start_datetime', $start_datetime);
        }

        if ($end_datetime != '') {
            $cmd->bindValue(':end_datetime', $end_datetime);
        }

        //计算起始数据行
        if ($page <= 0) $page = 0;
        $start = ($page - 1) * $pagesize;

        $cmd->bindValue(':start', $start);
        $cmd->bindValue(':pagesize', $pagesize);

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 根据id获取特价活动
     * @param $id
     * @return null
     */
    public static function getBargainById($id)
    {
        $sql = 'SELECT * FROM t_activity_bargain WHERE id = :id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);

        $result = $cmd->queryOne();
        if ($result == false) {
            return null;
        }

        return $result;
    }

    /**
     * 获取当前机构特价活动的所有产品
     * @param $current_institution_code
     * @return array
     */
    public static function getBargainAllGoods($current_institution_code)
    {
        //is_shelved 1上架0下架
        //is_activity 0无1特价


        $sql = 'SELECT code,name,selling_price AS old_price,selling_price AS price,NULL AS is_top,NULL AS activity_id FROM t_goods 
                  WHERE is_shelved = 1 
                    AND is_activity = 0
                    AND institution_code = :institution_code
                 ORDER BY code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $current_institution_code);

        $result = $cmd->queryAll();

        return $result;
    }

    /**
     * 获取当前机构特价活动的产品
     * @param $bargain_id
     * @param $current_institution_code
     * @return array
     */
    public static function getBargainGoodsById($bargain_id, $current_institution_code)
    {
        $sql = 'SELECT * FROM (
                              SELECT
                                t1.code,
                                t1.name,
                                ifnull(t2.old_price, t1.selling_price) AS old_price,
                                ifnull(t2.price, t1.selling_price)     AS price,
                                t2.is_top,
                                t2.activity_id
                              FROM t_goods t1
                                INNER JOIN t_activity_bargain_goods t2
                                  ON t2.goods_code = t1.code AND t2.activity_id = :bargain_id
                              WHERE t1.is_shelved = 1 AND t1.institution_code = :institution_code
                              UNION ALL
                              SELECT
                                code,
                                name,
                                selling_price AS old_price,
                                selling_price AS price,
                                NULL          AS is_top,
                                NULL          AS activity_id
                              FROM t_goods
                              WHERE is_shelved = 1
                                    AND is_activity = 0
                                    AND institution_code = :institution_code
                            ) AS t';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':bargain_id', $bargain_id);
        $cmd->bindValue(':institution_code', $current_institution_code);

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取特价活动详情商品
     * @param $bargain_id
     * @param $current_institution_code
     * @return array
     */
    public static function getBargainDetailGoodsById($bargain_id, $current_institution_code)
    {
        $sql = 'SELECT
                    t1.code,
                    t1.name,
                    ifnull(t2.old_price, t1.selling_price) AS old_price,
                    ifnull(t2.price, t1.selling_price)     AS price,
                    t2.is_top,
                    t2.activity_id
                  FROM t_goods t1
                    INNER JOIN t_activity_bargain_goods t2
                      ON t2.goods_code = t1.code AND t2.activity_id = :bargain_id
                  WHERE t1.is_shelved = 1 AND t1.institution_code = :institution_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':bargain_id', $bargain_id);
        $cmd->bindValue(':institution_code', $current_institution_code);

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 保存特价活动
     * @param $bargain_id
     * @param $institution_code
     * @param $name
     * @param $description
     * @param $start_datetime
     * @param $end_datetime
     * @param $is_enabled
     * @param $goodslist
     * @return array
     */
    public static function saveBargain($bargain_id, $institution_code, $name, $description, $start_datetime, $end_datetime, $is_enabled, $goodslist)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        try {
            //编辑特价活动
            $cmd = \Yii::$app->db->createCommand('call p_save_bargain(:id,:institution_code,:name,:description,:start_datetime,:end_datetime,:is_enabled)');
            $cmd->bindValue(':id', $bargain_id);
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':name', $name);
            $cmd->bindValue(':description', $description);
            $cmd->bindValue(':start_datetime', $start_datetime);
            $cmd->bindValue(':end_datetime', $end_datetime);
            $cmd->bindValue(':is_enabled', $is_enabled);


            $bargain_id = $cmd->queryScalar();

            //保存特价活动产品
            if ($bargain_id != '') {

                //修改商品表中是否参加活动状态
                $sql = 'SELECT goods_code FROM t_activity_bargain_goods WHERE activity_id = :activity_id';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':activity_id', $bargain_id);
                $goods = $cmd->queryAll();

                if ($goods != false && count($goods) > 0) {
                    for ($i = 0; $i < count($goods); $i++) {
                        $sql = 'UPDATE t_goods SET is_activity = 0 WHERE code = :code AND institution_code = :institution_code';
                        $cmd = \Yii::$app->db->createCommand($sql);
                        $cmd->bindValue(':code', $goods[$i]['goods_code']);
                        $cmd->bindValue(':institution_code', $institution_code);
                        $result = $cmd->execute();
                    }
                }

                //清空特价活动产品
                $sql = 'DELETE FROM t_activity_bargain_goods WHERE activity_id = :activity_id';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':activity_id', $bargain_id);
                $cmd->execute();


                foreach ($goodslist as $goods) {
                    //判断该商品是否参与过活动
                    $sql = 'SELECT COUNT(*) FROM t_goods WHERE code = :code AND is_activity = 1 AND institution_code = :institution_code';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':code', $goods['code']);
                    $cmd->bindValue(':institution_code', $institution_code);
                    $result = $cmd->queryScalar();
                    if ($result > 0) {
                        $transaction->rollBack();
                        return ['status' => 1, 'message' => '商品：' . $goods['code'] . '包含在其它活动中'];
                    }

                    //插入特价活动产品
                    $sql = 'INSERT INTO t_activity_bargain_goods(id,activity_id,institution_code,goods_code,old_price,price,is_top) 
                                                         VALUES (uuid(),:activity_id,:institution_code,:goods_code,:old_price,:price,:is_top)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':activity_id', $bargain_id);
                    $cmd->bindValue(':institution_code', $institution_code);
                    $cmd->bindValue(':goods_code', $goods['code']);
                    $cmd->bindValue(':old_price', $goods['old_price']);
                    $cmd->bindValue(':price', $goods['price']);
                    $cmd->bindValue(':is_top', $goods['is_top']);
                    $cmd->execute();

                    //修改产品是否参与特价活动 is_activity 0无1特价
                    $sql = 'UPDATE t_goods SET is_activity = :is_activity WHERE code = :goods_code AND institution_code = :institution_code';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':is_activity', 1);
                    $cmd->bindValue(':goods_code', $goods['code']);
                    $cmd->bindValue(':institution_code', $institution_code);
                    $cmd->execute();
                }
            }


            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            return ['status' => 1, 'message' => '服务器错误'];
        }
        return ['status' => 0, 'message' => '执行成功'];
    }


    /**
     * 执行计划任务，当活动时间结束替换
     * @param $bargain_id
     * @return array
     */
    public static function autoUpdateBargain()
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        try {
            //获取未失效的特价活动
            $sql = 'SELECT id from t_activity_bargain WHERE is_enabled = 1 AND end_datetime <= now()';
            $cmd = \Yii::$app->db->createCommand($sql);
            $items = $cmd->queryAll();
            if ($items != null && count($items) > 0) {
                foreach ($items as $item) {
                    //修改活动为不启用
                    $sql = 'UPDATE t_activity_bargain SET is_enabled = 0 WHERE id = :bargain_id';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':bargain_id', $item['id']);
                    $result = $cmd->execute();
                    if ($result > 0) {
                        //修改商品为不参与活动
                        $sql = 'UPDATE t_goods g,t_activity_bargain_goods b
                                  SET g.is_activity = 0
                                WHERE g.code = b.goods_code AND g.institution_code = b.institution_code
                                AND b.activity_id = :activity_id';
                        $cmd = \Yii::$app->db->createCommand($sql);
                        $cmd->bindValue(':activity_id', $item['id']);
                        $result = $cmd->execute();
                    }
                }
            }


            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            return ['status' => 1, 'message' => '服务器错误'];
        }

        return ['status' => 0, 'message' => '执行成功'];

    }

    /**
     * 删除特价活动
     * @param $id
     * @return array
     */
    public static function delBargain($id)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        try {
            //修改特价活动是否删除状态
            $sql = 'UPDATE t_activity_bargain set is_deleted = 1 WHERE id = :id';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $id);
            $result = $cmd->execute();

            //修改商品表中是否参加活动状态
            $sql = 'SELECT goods_code,institution_code FROM t_activity_bargain_goods WHERE activity_id = :activity_id';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':activity_id', $id);
            $goods = $cmd->queryAll();

            if ($goods != false && count($goods) > 0) {
                for ($i = 0; $i < count($goods); $i++) {
                    $sql = 'UPDATE t_goods SET is_activity = 0 WHERE code = :code AND institution_code = :institution_code';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':code', $goods[$i]['goods_code']);
                    $cmd->bindValue(':institution_code', $goods[$i]['institution_code']);
                    $result = $cmd->execute();
                }
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            return ['status' => 1, 'message' => '服务器错误'];
        }
        return ['status' => 0, 'message' => '执行成功'];
    }

    /**
     * 同步特价活动
     * @param $from_institution_code
     * @param $to_institution_code
     */
    public static function sysBargain($from_institution_code, $to_institution_code)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        try {
            //删除已有的活动
            $sql = 'DELETE FROM t_activity_bargain WHERE institution_code = :institution_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $from_institution_code);
            $cmd->execute();


            $sql = 'SELECT * FROM t_activity_bargain WHERE is_enabled = 1 AND institution_code = :institution_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $from_institution_code);

            $items = $cmd->queryAll();
            if ($items != null && count($items) > 0) {
                foreach ($items as $item) {
                    $sql = 'INSERT INTO integral.t_activity_bargain (id, institution_code, name, description, start_datetime, end_datetime, is_enabled, is_deleted) 
                        VALUES (uuid(), :institution_code, :name, :description, :start_datetime, :end_datetime, :is_enabled, :is_deleted)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':institution_code', $to_institution_code);
                    $cmd->bindValue(':name', $item['name']);
                    $cmd->bindValue(':description', $item['description']);
                    $cmd->bindValue(':start_datetime', $item['start_datetime']);
                    $cmd->bindValue(':end_datetime', $item['end_datetime']);
                    $cmd->bindValue(':is_enabled', $item['is_enabled']);
                    $cmd->bindValue(':is_deleted', $item['is_deleted']);

                    $cmd->execute();
                }
            }
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }

    }

    /**
     * 同步当前机构活动特价到下级机构
     * @return bool
     */
    public static function sysUnderInstitutionBargain($ins_code, $under_ins_codes)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        $result = 0;
        try {
            //当前机构的活动特价
            $sql = 'SELECT * FROM t_activity_bargain WHERE institution_code = :institution_code AND is_enabled = 1 AND is_deleted = 0';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $ins_code);
            $items = $cmd->queryAll();

            foreach ($under_ins_codes as $under_ins_code) {
                //删除下级机构的活动特价及活动商品
                $sql = 'DELETE FROM t_activity_bargain WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $under_ins_code);
                $cmd->execute();

                //删除下级机构的活动商品
                $sql = 'DELETE FROM t_activity_bargain_goods WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $under_ins_code);
                $cmd->execute();

                //修改下级机构商品活动状态为0
                $sql = 'UPDATE t_goods SET is_activity = 0 WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $under_ins_code);
                $cmd->execute();

                foreach ($items as $item) {
                    //同步活动特价
                    $sql = 'INSERT INTO t_activity_bargain(id,institution_code,name,description,start_datetime,end_datetime,is_enabled,is_deleted)
                                VALUES (:id,:institution_code,:name,:description,:start_datetime,:end_datetime,:is_enabled,:is_deleted)';
                    $cmd = \Yii::$app->db->createCommand($sql);

                    $bargain_id = Tools::db_uuid();
                    $cmd->bindValue(':id', $bargain_id);
                    $cmd->bindValue(':institution_code', $under_ins_code);
                    $cmd->bindValue(':name', $item['name']);
                    $cmd->bindValue(':description', $item['description']);
                    $cmd->bindValue(':start_datetime', $item['start_datetime']);
                    $cmd->bindValue(':end_datetime', $item['end_datetime']);
                    $cmd->bindValue(':is_enabled', $item['is_enabled']);
                    $cmd->bindValue(':is_deleted', $item['is_deleted']);
                    $cmd->execute();

                    //同步活动商品
                    $sql = 'SELECT * FROM t_activity_bargain_goods WHERE institution_code = :institution_code AND activity_id = :activity_id';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':institution_code', $ins_code);
                    $cmd->bindValue(':activity_id', $item['id']);
                    $goods_list = $cmd->queryAll();
                    if (!empty($goods_list)) {
                        foreach ($goods_list as $goods) {
                            $sql = 'INSERT INTO t_activity_bargain_goods(id,activity_id,institution_code,goods_code,old_price,price,is_top)
                                        VALUES (:id,:activity_id,:institution_code,:goods_code,:old_price,:price,:is_top)';
                            $cmd = \Yii::$app->db->createCommand($sql);
                            $cmd->bindValue(':id', Tools::db_uuid());
                            $cmd->bindValue(':activity_id', $bargain_id);
                            $cmd->bindValue(':institution_code', $under_ins_code);
                            $cmd->bindValue(':goods_code', $goods['goods_code']);
                            $cmd->bindValue(':old_price', $goods['old_price']);
                            $cmd->bindValue(':price', $goods['price']);
                            $cmd->bindValue(':is_top', $goods['is_top']);
                            $cmd->execute();

                            //修改下级机构商品为活动商品
                            $sql = 'UPDATE t_goods SET is_activity = 1 WHERE institution_code = :institution_code AND code = :goods_code';
                            $cmd = \Yii::$app->db->createCommand($sql);
                            $cmd->bindValue(':institution_code', $under_ins_code);
                            $cmd->bindValue(':goods_code', $goods['goods_code']);
                            $cmd->execute();

                        }
                    }
                }
            }
            $result = 1;
            $transaction->commit();

        } catch (\Exception $e) {
            $transaction->rollBack();
            $result = 0;
        }

        return $result > 0;
    }
}